﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Xml;
using Sherwood.Content.Contracts;

namespace Sherwood.Content.UpdateScheduler
{
    public abstract class SqlRecordSourceBase : IRecordPullSource
    {
        public string ConnectionString { get; set; }

        protected SqlRecordSourceBase()
        {
        }

        protected SqlRecordSourceBase(string connectionString)
        {
            ConnectionString = connectionString;
        }

        public IEnumerable<string> GetAllRecords(UpdateContext context)
        {
            return GetRecords(context, null);
        }

        private IEnumerable<string> GetRecords(UpdateContext context, Action<SqlCommand> modifyCommand)
        {
            var conn = new SqlConnection(ConnectionString);
            conn.Open();
            var tran = conn.BeginTransaction(IsolationLevel);

            var cmd = conn.CreateCommand();
            cmd.Transaction = tran;
            SetCommandToSelectActiveContent(cmd);
            if (modifyCommand != null)
                modifyCommand(cmd);
            var reader = cmd.ExecuteReader();
            while (reader.Read())
            {
                var referenceId = Convert.ToString(reader[ReferenceIdColumnName]);
                foreach (var xml in PopulateRecordXmlForReferenceId(referenceId, reader, context))
                    yield return xml;
            }

            reader.Close();
            tran.Commit();
            conn.Close();
        }

        protected virtual IEnumerable<string> PopulateRecordXmlForReferenceId(string referenceId, SqlDataReader reader, UpdateContext context)
        {
            var record = new record();
            try
            {
                FillRecord(record, referenceId, reader);
            }
            catch (Exception ex)
            {
                context.OnHandleException(this, new UpdateExceptionEventArgs(referenceId, ex));
                yield break;
            }
            var xml = new StringBuilder();
            using (var writer = XmlWriter.Create(xml))
                record.Untyped.WriteTo(writer);
            yield return xml.ToString();
        }

        protected abstract string ReferenceIdColumnName { get; }

        protected virtual IsolationLevel IsolationLevel
        {
            get { return IsolationLevel.Snapshot; }
        }

        protected abstract void FillRecord(record record, string referenceId, SqlDataReader reader);
        protected abstract void SetCommandToSelectActiveContent(SqlCommand cmd);

        public string GetRecordByReferenceId(UpdateContext context, string referenceId)
        {
            return GetRecords(context, command => AddReferenceIdFiltering(command, referenceId)).FirstOrDefault();
        }

        protected abstract void AddReferenceIdFiltering(SqlCommand command, string referenceId);

        public IEnumerable<string> GetRecordsModifiedSince(UpdateContext context, DateTime firstModificationDateUtc)
        {
            var diff = DateTime.UtcNow.Subtract(firstModificationDateUtc);
            var allowedDiff = Math.Min(Math.Abs(diff.TotalMinutes), 10);

            firstModificationDateUtc = firstModificationDateUtc.ToLocalTime();

            return GetRecords(context,
                              command =>
                                  {
                                      command.CommandText += GetModifiedSinceFilterText();
                                      // allow up to 10 minutes time skewing between servers
                                      command.Parameters.Add("@dateModified", SqlDbType.DateTime).Value = firstModificationDateUtc.AddMinutes(-allowedDiff);
                                      command.Parameters.Add("@now", SqlDbType.DateTime).Value = DateTime.Now.AddMinutes(allowedDiff);
                                  });
        }

        /// <summary>
        /// Parameters @dateModified and @now are available
        /// </summary>
        /// <returns></returns>
        protected abstract string GetModifiedSinceFilterText();

        public IEnumerable<IRecord> GetDeletedRecords(IEnumerable<IRecord> referenceIds)
        {
            var conn = new SqlConnection(ConnectionString);
            conn.Open();

            var cmd = conn.CreateCommand();
            var records = referenceIds.ToList();
            var ids = records.Where(r => !string.IsNullOrEmpty(r.ReferenceId)).Select(r => r.ReferenceId);
            SetCommandTextToSelectExistingIds(cmd, ids);

            var existing = new List<string>();
            using (var reader = cmd.ExecuteReader())
            {
                while (reader.Read())
                {
                    var referenceId = reader.GetInt32(0).ToString();
                    if (ValidateRecordExistance(referenceId))
                        existing.Add(referenceId);
                }
            }

            for (int i = 0; i < records.Count; i++)
            {
                if (existing.Contains(records[i].ReferenceId))
                    records.RemoveAt(i--);
            }

            conn.Close();
            return records;
        }

        protected virtual bool ValidateRecordExistance(string referenceId)
        {
            return true;
        }

        protected abstract void SetCommandTextToSelectExistingIds(SqlCommand cmd, IEnumerable<string> ids);
    }
}